package com.huellas.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import com.huellas.bean.CatalogoTareaBean;
import com.huellas.excepcion.DAOExcepcion;
import com.huellas.util.ConexionBD;

public class CatalogoTareaDAO extends BaseDAO{

	//Metodo para insertar catalogo de tarea
	public void insertarCatalogoTarea(CatalogoTareaBean catalogoTareaBean) throws DAOExcepcion {
		String query = "insert into Vacuna(NoCatalogoTarea, SsPrecioTarea) values (?,?)";
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, catalogoTareaBean.getNoCatalogoTarea());
			preparedStatement.setDouble(2, catalogoTareaBean.getSsPrecioTarea());
			
			int i = preparedStatement.executeUpdate();
			
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			
			// Obtener el ultimo id
			int id = 0;
			query = "select last_insert_id()";
			preparedStatement = connection.prepareStatement(query);
			resultSet = preparedStatement.executeQuery();
			
			if (resultSet.next()) {
				id = resultSet.getInt(1);
			}
			
			catalogoTareaBean.setCoCatalogoTarea(id);
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
	}
	
	//Metodo para buscar vacunas
	public Collection<CatalogoTareaBean> buscarCatalogoTarea(String NoCatalogoTarea) throws DAOExcepcion{
		String query = "select NoCatalogoTarea, SsPrecioTarea from CatalogoTarea where NoCatalogoTarea like ?";
		Collection<CatalogoTareaBean> listaCatalogoTarea = new ArrayList<CatalogoTareaBean>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, "%" + NoCatalogoTarea + "%");
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				CatalogoTareaBean catalogoTareaBean = new CatalogoTareaBean();
				catalogoTareaBean.setNoCatalogoTarea(resultSet.getString("NoCatalogoTarea"));
				catalogoTareaBean.setSsPrecioTarea(resultSet.getDouble("SsPrecioTarea"));
				listaCatalogoTarea.add(catalogoTareaBean);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
			
		return listaCatalogoTarea;
	}
	
	//Metodo para buscar tarea
	public Collection<CatalogoTareaBean> listarCatalogoTarea() throws DAOExcepcion{
		String query = "select NoCatalogoTarea from CatalogoTarea";
		Collection<CatalogoTareaBean> listaCatalogoTarea = new ArrayList<CatalogoTareaBean>();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				CatalogoTareaBean catalogoTareaBean = new CatalogoTareaBean();
				catalogoTareaBean.setNoCatalogoTarea(resultSet.getString("NoCatalogoTarea"));
				listaCatalogoTarea.add(catalogoTareaBean);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally{
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
		
		return listaCatalogoTarea;
	}
	
	//Metodo para obtener registro de la tabla CatalogoTarea
	public CatalogoTareaBean obtenerRegistroCatalogoTarea(String NoCatalogoTarea, double SsPrecioTarea) throws DAOExcepcion{
		CatalogoTareaBean catalogoTareaBean = new CatalogoTareaBean();
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
			
		try{
			String query = "select CoCatalogoTarea, NoCatalogoTarea, SsPrecioTarea from CatalogoTarea where NoCatalogoTarea=? and SsPrecioTarea=?";
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, NoCatalogoTarea);
			preparedStatement.setDouble(2, SsPrecioTarea);
			resultSet = preparedStatement.executeQuery();
				
			if(resultSet.next()){
				catalogoTareaBean.setCoCatalogoTarea(resultSet.getInt("CoCatalogoTarea"));
				catalogoTareaBean.setNoCatalogoTarea(resultSet.getString("NoCatalogoTarea"));
				catalogoTareaBean.setSsPrecioTarea(resultSet.getDouble("SsPrecioTarea"));
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally {
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
		
		return catalogoTareaBean;
	}
	
	//Metodo para obtener el codigo de catalogo de tarea
	public int obtenerCoCatalogoTarea(String NoCatalogoTarea) throws DAOExcepcion{
		int coCatalogoTarea = 0;
		
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		
		try{
			String query = "select CoCatalogoTarea from CatalogoTarea where NoCatalogoTarea=?";
			connection = ConexionBD.obtenerConexion();
			preparedStatement = connection.prepareStatement(query);
			preparedStatement.setString(1, NoCatalogoTarea);
			resultSet = preparedStatement.executeQuery();
				
			if(resultSet.next()){
				coCatalogoTarea = resultSet.getInt(1);
			}
		}catch(SQLException e){
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		}finally {
			this.cerrarResultSet(resultSet);
			this.cerrarStatement(preparedStatement);
			this.cerrarConexion(connection);
		}
			
		return coCatalogoTarea;
	}
}
